package com.luoying.itew.db;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.google.gson.Gson;
import com.luoying.itew.bean.MonitorBean;
import com.luoying.itew.bean.PointCityBean;
import com.luoying.itew.bean.UserInfo;
import com.luoying.itew.common.AntaiApplication;
import com.luoying.itew.util.LogUtils;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.stream.Collectors;


public class DBManager {
    /**
     * 数据库名称
     */
    static final String DB_ANME = "antai";
    /**
     * 数据库版本
     */
    static final int VERSION = 1;
    private static DBManager manager;

    private DataBaseHelper dataBaseHelper;

    public static DBManager getInstance(){
        if(manager==null){
            synchronized (DBManager.class){
                if (manager==null){
                    manager = new DBManager();
                }
            }
        }
        return manager;
    }

    public void creatDB(){
        dataBaseHelper = new DataBaseHelper(AntaiApplication.context, DB_ANME, VERSION, new DBListener() {
            @Override
            public void onCreate(SQLiteDatabase db) {
                //数据创建
                creatUserTable(db);
                createPointTable(db);
                createCityTable(db);
            }

            @Override
            public void onUpgrade(int oldVersion, int newVersion) {
                //数据升级或者更新
            }
        });
        dataBaseHelper.onCreate(dataBaseHelper.getWritableDatabase());
    }

    /**
     * 创建用户表
     * @param db
     */
    private void creatUserTable(SQLiteDatabase db){
        try {
            //创建用户表
            StringBuilder userSql = new StringBuilder();
            userSql.append("CREATE TABLE IF NOT EXISTS ").append(DBConstant.UserTable.TABLE_NAME).append(" (")
                    .append(DBConstant.UserTable.USER_ID).append(" integer primary key ,")
                    .append(DBConstant.UserTable.USER_LOGIN_NAME).append(" char(50),")
                    .append(DBConstant.UserTable.USER_MOBILE).append(" char(50),")
                    .append(DBConstant.UserTable.USER_NAME).append(" char(50),")
                    .append(DBConstant.UserTable.USER_CITY).append(" char(50),")
                    .append(DBConstant.UserTable.USER_LEVEL).append(" integer,")
                    .append(DBConstant.UserTable.USER_TYPE).append(" integer,")
                    .append(DBConstant.UserTable.USER_POSITION).append(" char(50) )");
            db.execSQL(userSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建监测点表
     * @param db
     */
    private void createPointTable(SQLiteDatabase db){
        try {
            StringBuilder userSql = new StringBuilder();
            userSql.append("CREATE TABLE IF NOT EXISTS ").append(DBConstant.PointTable.TABLE_NAME).append(" (")
                    .append(DBConstant.PointTable.POINT_ID).append(" integer primary key ,")
                    .append(DBConstant.PointTable.POINT_AREA_ID).append(" integer ,")
                    .append(DBConstant.PointTable.POINT_NAME).append(" char(50) ,")
                    .append(DBConstant.PointTable.POINT_STATUS).append(" integer ,")
                    .append(DBConstant.PointTable.POINT_COEFFICIENT).append(" integer ,")
                    .append(DBConstant.PointTable.POINT_TUNNELS).append(" integer ,")
                    .append(DBConstant.PointTable.POINT_TYPEDESC).append(" integer ,")
                    .append(DBConstant.PointTable.POINT_HEIGHT).append(" integer )");
            db.execSQL(userSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建城市区域表
     * @param db
     */
    private void createCityTable(SQLiteDatabase db){
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE IF NOT EXISTS ").append(DBConstant.CityTable.TABLE_NAME).append(" (")
                    .append(DBConstant.CityTable.STREET_ID).append(" integer primary key ,")
                    .append(DBConstant.CityTable.CITY).append(" char(50) ,")
                    .append(DBConstant.CityTable.DISTRICT).append(" char(50) ,")
                    .append(DBConstant.CityTable.STREET).append(" char(50) ,")
                    .append(DBConstant.CityTable.FULL_NAME).append(" char(50) )");
            db.execSQL(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 保存用户对象
     */
    public void saveUser(UserInfo userInfo){
        try {
            StringBuilder sql = new StringBuilder();
            SQLiteDatabase database = dataBaseHelper.getWritableDatabase();
            sql.append("insert into ").append(DBConstant.UserTable.TABLE_NAME)
                    .append("(")
                    .append(DBConstant.UserTable.USER_ID).append(",")
                    .append(DBConstant.UserTable.USER_LOGIN_NAME).append(",")
                    .append(DBConstant.UserTable.USER_MOBILE).append(",")
                    .append(DBConstant.UserTable.USER_NAME).append(",")
                    .append(DBConstant.UserTable.USER_CITY).append(",")
                    .append(DBConstant.UserTable.USER_LEVEL).append(",")
                    .append(DBConstant.UserTable.USER_TYPE).append(",")
                    .append(DBConstant.UserTable.USER_POSITION).append(")")
                    .append(" values (?,?,?,?,?,?,?,?) ");
            Object[] value = new Object[]{userInfo.getUserId(),userInfo.getLoginName(),
                    userInfo.getUserMobile(), userInfo.getUserName(),
                    userInfo.getUserCity(),userInfo.getUserLevel(),
                    userInfo.getUserType(),userInfo.getUserPosition()};
            cleanTable(DBConstant.UserTable.TABLE_NAME);
            database.execSQL(sql.toString(),value);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取用户对象
     */
    public UserInfo getUser (){
        UserInfo userInfo = null;
        try {
            Cursor cursor = dataBaseHelper.getWritableDatabase().query(DBConstant.UserTable.TABLE_NAME,
                    null,null,null,null,null,null);
            if (cursor.getColumnCount()>0){
                while (cursor.moveToNext()){
                    userInfo = new UserInfo();
                    userInfo.setUserId(cursor.getInt(cursor.getColumnIndex(DBConstant.UserTable.USER_ID)));
                    userInfo.setLoginName(cursor.getString(cursor.getColumnIndex(DBConstant.UserTable.USER_LOGIN_NAME)));
                    userInfo.setUserMobile(cursor.getString(cursor.getColumnIndex(DBConstant.UserTable.USER_MOBILE)));
                    userInfo.setUserName(cursor.getString(cursor.getColumnIndex(DBConstant.UserTable.USER_NAME)));
                    userInfo.setUserCity(cursor.getString(cursor.getColumnIndex(DBConstant.UserTable.USER_CITY)));
                    userInfo.setUserLevel(cursor.getInt(cursor.getColumnIndex(DBConstant.UserTable.USER_LEVEL)));
                    userInfo.setUserType(cursor.getInt(cursor.getColumnIndex(DBConstant.UserTable.USER_TYPE)));
                    userInfo.setUserPosition(cursor.getString(cursor.getColumnIndex(DBConstant.UserTable.USER_POSITION)));
                }
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dataBaseHelper.getWritableDatabase().close();
        }
        return userInfo;
    }


    /**
     * 保存监测点集合
     * @param list
     */
    public void savePointList(List<MonitorBean> list){
        SQLiteDatabase db = dataBaseHelper.getWritableDatabase();
        try {
            cleanTable(DBConstant.PointTable.TABLE_NAME);
            //开启事务
            db.beginTransaction();
            for (MonitorBean monitorBean : list) {
                StringBuilder sql = new StringBuilder();
                sql.append("insert into ").append(DBConstant.PointTable.TABLE_NAME)
                        .append("(")
                        .append(DBConstant.PointTable.POINT_ID).append(",")
                        .append(DBConstant.PointTable.POINT_AREA_ID).append(",")
                        .append(DBConstant.PointTable.POINT_NAME).append(",")
                        .append(DBConstant.PointTable.POINT_STATUS).append(",")
                        .append(DBConstant.PointTable.POINT_COEFFICIENT).append(",")
                        .append(DBConstant.PointTable.POINT_TUNNELS).append(",")
                        .append(DBConstant.PointTable.POINT_TYPEDESC).append(",")
                        .append(DBConstant.PointTable.POINT_HEIGHT).append(")")
                        .append(" values (?,?,?,?,?,?,?,?) ");
                Object[] value = new Object[]{monitorBean.getMonitorPointId(),monitorBean.getMonitorAreaId(),
                        monitorBean.getMonitorPointName(),monitorBean.getMonitorStatus(),
                        monitorBean.getWarnCoefficient(),monitorBean.getTunnels(),monitorBean.getMaterialTypeDesc(),
                        monitorBean.getMonitorHeight()};
                db.execSQL(sql.toString(),value);
            }
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 获取监测点集合
     * @return
     */
    public List<MonitorBean> getPointList(){
        List<MonitorBean> list = new ArrayList<>();
        try {
            Cursor cursor = dataBaseHelper.getWritableDatabase().query(DBConstant.PointTable.TABLE_NAME,
                    null,null,null,null,null,null);
            if (cursor.getColumnCount()>0){
                while (cursor.moveToNext()){
                    MonitorBean bean = new MonitorBean();
                    bean.setMonitorPointId(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_ID)));
                    bean.setMonitorAreaId(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_AREA_ID)));
                    bean.setMonitorPointName(cursor.getString(cursor.getColumnIndex(DBConstant.PointTable.POINT_NAME)));
                    bean.setMonitorStatus(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_STATUS)));
                    bean.setWarnCoefficient(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_COEFFICIENT)));
                    bean.setTunnels(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_TUNNELS)));
                    bean.setMaterialTypeDesc(cursor.getString(cursor.getColumnIndex(DBConstant.PointTable.POINT_TYPEDESC)));
                    bean.setMonitorHeight(cursor.getInt(cursor.getColumnIndex(DBConstant.PointTable.POINT_HEIGHT)));
                    list.add(bean);
                }
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dataBaseHelper.getWritableDatabase().close();
        }
        return list;
    }

    /**
     * 保存城市数据
     */
    public void saveCityList(List<PointCityBean> list){
        SQLiteDatabase db = dataBaseHelper.getWritableDatabase();
        try {
            //数据去重
            List<PointCityBean> newList = new ArrayList<>();
            Set set = new HashSet();
            for (PointCityBean bean : list) {
                if(set.add(bean)){
                    newList.add(bean);
                }
            }
            set.clear();
            //清除表中的数据
            cleanTable(DBConstant.CityTable.TABLE_NAME);
            //开启事务
            db.beginTransaction();
            for (PointCityBean bean : newList) {
                StringBuilder sql = new StringBuilder();
                sql.append("insert into ").append(DBConstant.CityTable.TABLE_NAME)
                        .append("(")
                        .append(DBConstant.CityTable.STREET_ID).append(",")
                        .append(DBConstant.CityTable.CITY).append(",")
                        .append(DBConstant.CityTable.DISTRICT).append(",")
                        .append(DBConstant.CityTable.STREET).append(",")
                        .append(DBConstant.CityTable.FULL_NAME).append(" )")
                        .append(" values (?,?,?,?,?) ");
                Object[] value = new Object[]{bean.getStreetId(), bean.getCityName(),
                        bean.getDistrictName(), bean.getStreetName(),bean.getFullName()};
                db.execSQL(sql.toString(),value);
            }
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }


    /**
     * 获取城市区域集合
     * @return
     */
    public List<PointCityBean> getCityList(){
        List<PointCityBean> list = new ArrayList<>();
        try {
            Cursor cursor = dataBaseHelper.getWritableDatabase().query(DBConstant.CityTable.TABLE_NAME,
                    null,null,null,null,null,null);
            if (cursor.getColumnCount()>0){
                while (cursor.moveToNext()){
                    PointCityBean bean = new PointCityBean();
                    bean.setStreetId(cursor.getInt(cursor.getColumnIndex(DBConstant.CityTable.STREET_ID)));
                    bean.setCityName(cursor.getString(cursor.getColumnIndex(DBConstant.CityTable.CITY)));
                    bean.setDistrictName(cursor.getString(cursor.getColumnIndex(DBConstant.CityTable.DISTRICT)));
                    bean.setStreetName(cursor.getString(cursor.getColumnIndex(DBConstant.CityTable.STREET)));
                    bean.setFullName(cursor.getString(cursor.getColumnIndex(DBConstant.CityTable.FULL_NAME)));
                    list.add(bean);
                }
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dataBaseHelper.getWritableDatabase().close();
        }
        return list;
    }

    /**
     * 清除表中数据
     */
    public void cleanTable(String  tableName){
        StringBuilder sql  = new StringBuilder("delete from ").append(tableName);
        try {
            dataBaseHelper.getWritableDatabase().execSQL(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
